﻿using System;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Collections.Generic;
using Dapper;
using API_Service.DBHelper;

namespace API_Service.BizLogic.WipLogApi
{
    public class RouteDesigerService : BaseLogicService
    {
        public RouteDesigerService() { }
        public ExecutionResult<dynamic> GetRouteList()
        {
            var result = new ExecutionResult<dynamic>();

            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {

                    conn.Open();

                    result.Datas = conn.Query("select route_code,route_name from c_route_name_t order by create_time asc").ToArray();
                    
                    result.Status = true;

                }
                catch (Exception ex)
                {
                    Logger.WriteError(MethodBase.GetCurrentMethod(), ex);
                    result.Status = false;
                    result.Message = "Error: " + ex.Message;
                }
                finally
                {
                    conn.Close();
                }
            }

            return result;
        }
        public ExecutionResult<dynamic> GetRouteByID(string routecode)
        {
            var result = new ExecutionResult<dynamic>();

            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {

                    conn.Open();

                    result.Datas = conn.Query("select * from c_route_name_t where route_code=@routecode", new {routecode=routecode }).ToArray();

                    result.Status = true;

                }
                catch (Exception ex)
                {
                    Logger.WriteError(MethodBase.GetCurrentMethod(), ex);
                    result.Status = false;
                    result.Message = "Error: " + ex.Message;
                }
                finally
                {
                    conn.Close();
                }
            }

            return result;
        }
        public ExecutionResult<dynamic> GetRouteByName(string routename)
        {
            var result = new ExecutionResult<dynamic>();

            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {

                    conn.Open();

                    result.Datas = conn.Query("select count(route_name) from c_route_name_t where route_name=@routename", 
                                                                 new { routename = routename }).ToArray();

                    result.Status = true;

                }
                catch (Exception ex)
                {
                    Logger.WriteError(MethodBase.GetCurrentMethod(), ex);
                    result.Status = false;
                    result.Message = "Error: " + ex.Message;
                }
                finally
                {
                    conn.Close();
                }
            }

            return result;
        }
        public ExecutionResult<dynamic> GetRouteDesignList()
        {
            var result = new ExecutionResult<dynamic>();

            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {

                    conn.Open();

                    result.Datas = conn.Query("select distinct b.route_name,b.route_code  from dbo.c_route_design_t a, dbo.c_route_name_t b where a.route_code=b.route_code").ToArray();

                    result.Status = true;

                }
                catch (Exception ex)
                {
                    Logger.WriteError(MethodBase.GetCurrentMethod(), ex);
                    result.Status = false;
                    result.Message = "Error: " + ex.Message;
                }
                finally
                {
                    conn.Close();
                }
            }

            return result;
        }
        public ExecutionResult<dynamic> LoadRouteDesign(string routecode)
        {
            var result = new ExecutionResult<dynamic>();

            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {

                    conn.Open();

                    result.Datas = conn.Query("select * from dbo.c_route_design_t where route_code=@routecode", new { routecode = routecode }).ToArray();

                    result.Status = true;

                }
                catch (Exception ex)
                {
                    Logger.WriteError(MethodBase.GetCurrentMethod(), ex);
                    result.Status = false;
                    result.Message = "Error: " + ex.Message;
                }
                finally
                {
                    conn.Close();
                }
            }

            return result;
        }
        public ExecutionResult<dynamic> GetRouteDesignListByID(string routecode)
        {
            var result = new ExecutionResult<dynamic>();

            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {

                    conn.Open();

                    result.Datas = conn.Query("select a.route_info  from dbo.c_route_design_t a, dbo.c_route_name_t b where a.route_code=b.route_code and a.route_code=@routecode",
                                                                new { routecode=routecode}).ToArray();

                    result.Status = true;

                }
                catch (Exception ex)
                {
                    Logger.WriteError(MethodBase.GetCurrentMethod(), ex);
                    result.Status = false;
                    result.Message = "Error: " + ex.Message;
                }
                finally
                {
                    conn.Close();
                }
            }

            return result;
        }
        public ExecutionResult<dynamic> UpdateRouteName(string viewModel)
        {
            var result = new ExecutionResult<dynamic>();

            dynamic vm = Newtonsoft.Json.JsonConvert.DeserializeObject(viewModel);

            IDbTransaction transaction = null;

            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {

                    conn.Open();

                    transaction = conn.BeginTransaction();

                    int  res = conn.Execute(@"update c_route_name_t set route_name=@route_name, route_desc=@route_desc, update_user=@update_user,
                                                               update_time=getdate()  where route_code=@routecode", 
                                                               new {
                                                                   routecode = (string)vm.route_code,
                                                                   route_name = (string)vm.route_name,
                                                                   route_desc = (string)vm.route_desc,
                                                                   update_user = (string)vm.create_user,
                                                               },transaction);

                    result.Status = true;
                    result.Message = "route info update ok.";
                    transaction.Commit();

                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    Logger.WriteError(MethodBase.GetCurrentMethod(), ex);
                    result.Status = false;
                    result.Message = "Error: " + ex.Message;
                }
                finally
                {
                    conn.Close();
                }
            }

            return result;
        }
        public ExecutionResult<dynamic> DeleteRouteInfo(string routecode)
        {
            var result = new ExecutionResult<dynamic>();

            IDbTransaction transaction = null;

            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {

                    conn.Open();

                    transaction = conn.BeginTransaction();

                    int res = conn.Execute("delete from c_route_control_t where route_code=@routecode", new { routecode = routecode }, transaction);

                    res = conn.Execute("delete from c_route_design_t where route_code=@routecode", new { routecode = routecode }, transaction);

                    res = conn.Execute("delete from c_route_name_t where route_code=@routecode", new { routecode = routecode }, transaction);

                    result.Status = true;
                    result.Message = "route info delete ok.";
                    transaction.Commit();

                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    Logger.WriteError(MethodBase.GetCurrentMethod(), ex);
                    result.Status = false;
                    result.Message = "Error: " + ex.Message;
                }
                finally
                {
                    conn.Close();
                }
            }

            return result;
        }
        public ExecutionResult<dynamic> SaveRouteDesignInfo(string routecode, byte[] routeinfo, string updateuser)
        {
            var result = new ExecutionResult<dynamic>();

            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {

                    conn.Open();

                    int res = conn.Query("select route_code from dbo.c_route_design_t where route_code=@routecode", 
                                                                            new { routecode = routecode }).Count();
                    if (res > 0)
                    {
                        result.ExecuteCount = conn.Execute("update dbo.c_route_design_t set route_info=@route_info, update_time=getdate(), " +
                                                                               "update_user=@update_user " +
                                                                               "where route_code=@route_code",
                                                                                new
                                                                                {
                                                                                    route_code = routecode,
                                                                                    route_info = routeinfo,
                                                                                    update_user = updateuser
                                                                                });
                    }
                    else
                    {
                        result.ExecuteCount = conn.Execute("insert into dbo.c_route_design_t(route_code,create_time,create_user,route_info)" +
                                                                              "values (@route_code,@create_time,@create_user,@route_info)",
                                                                               new
                                                                               {
                                                                                   route_code = routecode,
                                                                                   route_info = routeinfo,
                                                                                   create_time = DateTime.Now,
                                                                                   create_user = updateuser
                                                                               });
                    }

                    result.Status = true;
                    result.Message = "route save ok.";

                }
                catch (Exception ex)
                {
                    Logger.WriteError(MethodBase.GetCurrentMethod(), ex);
                    result.Status = false;
                    result.Message = "Error: " + ex.Message;
                }
                finally
                {
                    conn.Close();
                }
            }

            return result;
        }
        public ExecutionResult<dynamic> SaveRouteControlInfo(string viewModel)
        {
            var result = new ExecutionResult<dynamic>();

            dynamic vm = Newtonsoft.Json.JsonConvert.DeserializeObject(viewModel);

            IDbTransaction transaction = null;

            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {

                    conn.Open();

                    transaction = conn.BeginTransaction();

                    int flag = 0;

                    foreach (var v in vm)
                    {
                        if (flag == 0)
                        {
                            int res = conn.Execute("delete from c_route_control_t where route_code=@routecode", new { routecode = (string)v.route_code }, transaction);
                            flag++;
                        }

                        result.ExecuteCount = conn.Execute(@"insert into c_route_control_t (route_code,group_name,group_next,state_flag,step_sequence,route_desc,create_user)
                                                                                        values(@route_code,@group_name,@group_next,@state_flag,@step_sequence,@route_desc,@create_user )",
                                                                                   new
                                                                                   {
                                                                                       route_code = (string)v.route_code,
                                                                                       group_name = (string)v.group_name,
                                                                                       group_next = (string)v.group_next,
                                                                                       state_flag = (string)v.state_flag,
                                                                                       step_sequence = (string)v.step_sequence,
                                                                                       route_desc = (string)v.route_desc,
                                                                                       create_user = (string)v.create_user,
                                                                                   }, transaction);
                    }
                    
                    result.Status = true;
                    result.Message = "route control info save ok.";
                    transaction.Commit();

                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    Logger.WriteError(MethodBase.GetCurrentMethod(), ex);
                    result.Status = false;
                    result.Message = "Error: " + ex.Message;
                }
                finally
                {
                    conn.Close();
                }
            }

            return result;
        }
        public ExecutionResult<dynamic> SaveRouteNameInfo(string viewModel)
        {
            var result = new ExecutionResult<dynamic>();

            dynamic vm = Newtonsoft.Json.JsonConvert.DeserializeObject(viewModel);

            IDbTransaction transaction = null;

            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {

                    conn.Open();

                    transaction = conn.BeginTransaction();

                    int res = conn.Query("select * from c_route_name_t where route_code=@routecode ", new { routecode = (string)vm.route_code }).Count();

                    if (res > 0)
                    {
                        result.ExecuteCount = conn.Execute(@"update c_route_name_t set 
                                                                                        first_group=@first_group, end_group=@end_group,update_time=getdate(), update_user=@update_user 
                                                                                        where route_code=@route_code",
                                                                                        new
                                                                                        {
                                                                                            route_code = (string)vm.route_code,
                                                                                            first_group = (string)vm.first_group,
                                                                                            end_group = (string)vm.end_group,                                                         
                                                                                            update_user = (string)vm.create_user,
                                                                                        });
                    }

                    result.Status = true;
                    result.Message = "route control info save ok.";
                    transaction.Commit();

                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    Logger.WriteError(MethodBase.GetCurrentMethod(), ex);
                    result.Status = false;
                    result.Message = "Error: " + ex.Message;
                }
                finally
                {
                    conn.Close();
                }
            }

            return result;
        }
        public ExecutionResult<dynamic> InsertRouteNameInfo(string viewModel)
        {
            var result = new ExecutionResult<dynamic>();

            dynamic vm = Newtonsoft.Json.JsonConvert.DeserializeObject(viewModel);

            IDbTransaction transaction = null;

            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {

                    conn.Open();

                    transaction = conn.BeginTransaction();

                    int res = conn.Execute(@"insert into c_route_name_t (route_code,route_name,route_desc,create_user)
                                                           values(@route_code,@route_name,@route_desc,@create_user)",
                                                           new
                                                           {
                                                               route_code = (string)vm.route_code,
                                                               route_name = (string)vm.route_name,
                                                               route_desc = (string)vm.route_desc,
                                                               create_user = (string)vm.create_user
                                                           }, transaction);

                    result.Status = true;
                    result.Message = "route name info insert ok.";
                    transaction.Commit();

                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    Logger.WriteError(MethodBase.GetCurrentMethod(), ex);
                    result.Status = false;
                    result.Message = "Error: " + ex.Message;
                }
                finally
                {
                    conn.Close();
                }
            }

            return result;
        }

    }
}